#!/bin/bash
APP=edu

if [ -n "$2" ] ;then
   do_date=$2
else
   echo "请传入日期参数"
   exit
fi

# 这是sql语句
ads_complete_num="
insert overwrite table ads_complete_num
select
       '${do_date}',
       course_id,
       course_complete_user_num,
       chapter_complete_num
from dws_finishcourse_num_1d where dt = '${do_date}';
"

ads_test_info_by_exam="
insert overwrite table ads_test_info_by_exam
select *
from ads_test_info_by_exam
where dt != '${do_date}'
union
select *
from (
         select dt,
                1                                recent_days,
                paper_id,
                paper_title,
                course_id,
                course_name,
                round(score / num, 2)            avg_score,
                round(duration_sec_sum / num, 2) avg_duration_sec,
                user_num
         from dws_test_exam_1d
         where dt = '${do_date}'
     ) t1
union
(
    select dt,
           recent_days,
           paper_id,
           paper_title,
           course_id,
           course_name,
           round(if(recent_days = 7, score_7d / num_7d, score_30d / num_30d), 2) avg_score,
           round(if(recent_days = 7, duration_sec_sum_7d / num_7d, duration_sec_sum_30d /
                                                                     num_30d))   avg_duration_sec,
           if(recent_days = 7, user_num_7d, user_num_30d)                        user_num
    from dws_test_exam_nd
             lateral view explode(array(7, 30)) tmp as recent_days
    where dt = '${do_date}'
);
"

ads_test_info_by_scorestage="
insert overwrite table ads_test_info_by_scorestage
select *
from ads_test_info_by_scorestage
where dt != '${do_date}'
union
select *
from (
         select dt,
                1                                         recent_days,
                course_id,
                course_name,
                -- 如果100 也算90-100区间内
                if(score_stage = 10, 9, score_stage) * 10 score_stage,
                num,
                user_num
         from dws_test_course_scorestage_1d
         where dt = '${do_date}'
     ) t1
union
(
    select dt,
           recent_days,
           course_id,
           course_name,
           if(score_stage = 10, 9, score_stage) * 10      score_stage,
           if(recent_days = 7, num_7d, num_30d)           num,
           if(recent_days = 7, user_num_7d, user_num_30d) user_num
    from dws_test_course_scorestage_nd
             lateral view explode(array(7, 30)) tmp as recent_days
    where dt = '${do_date}'
);
"


ads_test_info_by_question="
insert overwrite table ads_test_info_by_question
select *
from ads_test_info_by_question
where dt != '${do_date}'
union
select *
from (
         select dt,
                1                                       recent_days,
                question_id,
                question_txt,
                round(correct_num / total_num * 100, 2) correct_rate
         from dws_test_question_1d
         where dt = '${do_date}'
     ) t1
union
(
    select dt,
           recent_days,
           question_id,
           question_txt,
           round(if(tmp.recent_days = 7, correct_num_7d / total_num_7d, correct_num_30d / total_num_30d) * 100,
                 2) correct_rate
    from dws_test_question_nd
             lateral view explode(array(7, 30)) tmp as recent_days
    where dt = '${do_date}'
);
"
ads_play_course_stats="
insert overwrite table ads_play_course_stats
select
*
from ads_play_course_stats where dt != '${do_date}'
union all
select
    dt,
    t2.recent_days,
    t2.course_id,
    cast(play_amount as BIGINT),
    cast(avg_sec as DECIMAL(16,2)),
    cast(user_amount as BIGINT)
from (
         select '${do_date}' dt,
                recent_days,
                course_id,
                count(case recent_days
                          when 1 then if(dt = '${do_date}', id, null)
                          when 7 then if(dt > date_sub('${do_date}', 7), id, null)
                          else id
                    end
                    )        play_amount,
                count(distinct case recent_days
                                   when 1 then if(dt = '${do_date}', id, null)
                                   when 7 then if(dt > date_sub('${do_date}', 7), id, null)
                                   else id
                    end
                    )        user_amount
         from dwd_user_video_inc
                  lateral view explode(array(1, 7, 30)) tmp as recent_days
         where dt <= '${do_date}'
           and dt > date_sub('${do_date}', 30)
         group by course_id, recent_days
     ) t2 left join (
    select course_id,
           recent_days,
           avg(max_sec) avg_sec
    from (
             select course_id,
                    recent_days,
                    max(if(recent_days = 1 and dt = '${do_date}', position_sec,
                             if(dt > date_sub('${do_date}', 7) and position_sec is null and recent_days = 7, 0,
                                  position_sec))) max_sec
             from dwd_user_video_inc
                      lateral view explode(array(1, 7, 30)) tmp as recent_days
             where dt > date_sub('${do_date}', 30)
               and dt <= '${do_date}'
             group by course_id, id, recent_days
         ) t1
    group by course_id, recent_days
) t3 on t2.recent_days = t3.recent_days and t2.course_id = t3.course_id;
"
ads_play_chapter_stats="
insert overwrite table ads_play_chapter_stats
select
*
from ads_play_chapter_stats where dt != '${do_date}'
union all
select
    dt,
    t2.recent_days,
    t2.chapter_id,
    cast(play_amount as BIGINT),
    cast(avg_sec as DECIMAL(16,2)),
    cast(user_amount as BIGINT)
from (
         select '${do_date}' dt,
                recent_days,
                chapter_id,
                count(case recent_days
                          when 1 then if(dt = '${do_date}', id, null)
                          when 7 then if(dt > date_sub('${do_date}', 7), id, null)
                          else id
                    end
                    )        play_amount,
                count(distinct case recent_days
                                   when 1 then if(dt = '${do_date}', id, null)
                                   when 7 then if(dt > date_sub('${do_date}', 7), id, null)
                                   else id
                    end
                    )        user_amount
         from dwd_user_video_inc
                  lateral view explode(array(1, 7, 30)) tmp as recent_days
         where dt <= '${do_date}'
           and dt > date_sub('${do_date}', 30)
         group by chapter_id, recent_days
     ) t2 left join (
    select chapter_id,
           recent_days,
           avg(max_sec) avg_sec
    from (
             select chapter_id,
                    recent_days,
                    max(if(recent_days = 1 and dt = '${do_date}', position_sec,
                             if(dt > date_sub('${do_date}', 7) and position_sec is null and recent_days = 7, 0,
                                  position_sec))) max_sec
             from dwd_user_video_inc
                      lateral view explode(array(1, 7, 30)) tmp as recent_days
             where dt > date_sub('${do_date}', 30)
               and dt <= '${do_date}'
             group by chapter_id, id, recent_days
         ) t1
    group by chapter_id, recent_days
) t3 on t2.recent_days = t3.recent_days and t2.chapter_id = t3.chapter_id;
"
ads_order_by_user="
insert overwrite table ads_order_by_user
select
*
from ads_order_by_user where dt != '${do_date}'
union all
select
    '${do_date}' dt,
    1 recent_days,
    sum(order_total_amount_1d) order_gmv,
    sum(order_count_1d) order_user,
    count(user_id) order_count
from dws_trade_user_province_order_1d
where dt = '${do_date}'
union all
select
    '${do_date}' dt,
    recent_days,
    sum(if(recent_days=7,order_total_amount_7d,order_total_amount_30d)) order_gmv,
    sum(if(recent_days=7,order_count_7d,order_count_30d)) order_count,
    count(if(recent_days=7 and order_count_7d = 0,null,user_id)) order_user
from dws_trade_user_province_order_nd
lateral view explode(array(7,30)) tmp as recent_days
where dt = '${do_date}'
group by recent_days;
"
ads_order_by_province="
insert overwrite table ads_order_by_province
select
*
from ads_order_by_province  where dt != '${do_date}'
union all
select
    '${do_date}' dt,
    1 recent_days,
    province_id,
    province_name,
    area_code,
    iso_code,
    iso_3166_2,
    sum(order_total_amount_1d) order_gmv,
    sum(order_count_1d) order_user,
    count(user_id) order_count
from dws_trade_user_province_order_1d
where dt = '${do_date}'
group by province_id, province_name, area_code, iso_code, iso_3166_2
union all
select
    '${do_date}' dt,
    recent_days,
    province_id,
    province_name,
    area_code,
    iso_code,
    iso_3166_2,
    sum(if(recent_days=7,order_total_amount_7d,order_total_amount_30d)) order_gmv,
    sum(if(recent_days=7,order_count_7d,order_count_30d)) order_count,
    count(if(recent_days=7 and order_count_7d = 0,null,user_id)) order_user
from dws_trade_user_province_order_nd
lateral view explode(array(7,30)) tmp as recent_days
where dt = '${do_date}'
group by recent_days, province_id, province_name, area_code, iso_code, iso_3166_2;
"
ads_order_category_lidan="
insert overwrite table ads_order_category_lidan partition(dt='${do_date}')
select category_name,
       count(if(dt='${do_date}',1,null)) order_count,
       count(distinct if(dt='${do_date}',user_id,null)) user_num,
       sum(if(dt='${do_date}',final_amount,0)) final_amount,
       count(if(dt>=date_add('${do_date}',-6),1,null)) order_count_7d,
       count(distinct if(dt>=date_add('${do_date}',-6),user_id,null)) user_num_7d,
       sum(if(dt>=date_add('${do_date}',-6),final_amount,0)) final_amount_7d,
       count(if(dt>=date_add('${do_date}',-29),1,null)) order_count_30d,
       count(distinct if(dt>=date_add('${do_date}',-29),user_id,null)) user_num_30d,
       sum(if(dt>=date_add('${do_date}',-29),final_amount,0)) final_amount_30d
from dws_order_category_lidan_1d
where dt='${do_date}'
group by category_name;
"
ads_user_retention_category_lidan="
insert overwrite table ads_user_retention_category_lidan partition(dt='${do_date}')
select a.category_name,
       a.try_dt,
       a.diff_day,
       b.try_user_num,
       a.pay_num/b.try_user_num
from
	(
		select category_name,
		       try_dt,
		       diff_day,
		       count(min_order_create_time) pay_num
		from dws_user_retention_category_lidan_1d
		where dt = '${do_date}' and diff_day>0
		group by category_name, try_dt, diff_day
	)a

left join(
    select category_name,
           try_dt,
           count(user_id) try_user_num
    from dws_user_retention_category_lidan_1d
    where dt='${do_date}' and diff_day>0
    group by category_name, try_dt
	         ) b
on a.category_name=b.category_name and a.try_dt=b.try_dt
"
ads_user_retention_subject_lidan="
insert overwrite table ads_user_retention_subject_lidan partition(dt='${do_date}')
select a.subject_name,
       a.try_dt,
       a.diff_day,
       b.try_user_num,
       a.pay_num/b.try_user_num
from
	(
		select subject_name,
		       try_dt,
		       diff_day,
		       count(min_order_create_time) pay_num
		from dws_user_retention_subject_lidan_1d
		where dt = '${do_date}' and (diff_day>0 or diff_day is null)
		group by subject_name, try_dt, diff_day
	)a

left join(
    select subject_name,
           try_dt,
           count(user_id) try_user_num
    from dws_user_retention_subject_lidan_1d
    where dt='${do_date}' and (diff_day>0 or diff_day is null)
    group by subject_name, try_dt
	         ) b
on a.subject_name=b.subject_name and a.try_dt=b.try_dt
"
ads_order_subject_lidan="
insert overwrite table ads_order_subject_lidan partition(dt='${do_date}')
select subject_name,
       count(if(dt='${do_date}',1,null)) order_count,
       count(distinct if(dt='${do_date}',user_id,null)) user_num,
       sum(if(dt='${do_date}',final_amount,0)) final_amount,
       count(if(dt>=date_add('${do_date}',-6),1,null)) order_count_7d,
       count(distinct if(dt>=date_add('${do_date}',-6),user_id,null)) user_num_7d,
       sum(if(dt>=date_add('${do_date}',-6),final_amount,0)) final_amount_7d,
       count(if(dt>=date_add('${do_date}',-29),1,null)) order_count_30d,
       count(distinct if(dt>=date_add('${do_date}',-29),user_id,null)) user_num_30d,
       sum(if(dt>=date_add('${do_date}',-29),final_amount,0)) final_amount_30d
from dws_order_subject_lidan_1d
where dt='${do_date}'
group by subject_name;
"

ads_order_course_lidan="
insert overwrite table ads_order_course_lidan partition(dt='${do_date}')
select course_name,
       count(if(dt='${do_date}',1,null)) order_count,
       count(distinct if(dt='${do_date}',user_id,null)) user_num,
       sum(if(dt='${do_date}',final_amount,0)) final_amount,
       count(if(dt>=date_add('${do_date}',-6),1,null)) order_count_7d,
       count(distinct if(dt>=date_add('${do_date}',-6),user_id,null)) user_num_7d,
       sum(if(dt>=date_add('${do_date}',-6),final_amount,0)) final_amount_7d,
       count(if(dt>=date_add('${do_date}',-29),1,null)) order_count_30d,
       count(distinct if(dt>=date_add('${do_date}',-29),user_id,null)) user_num_30d,
       sum(if(dt>=date_add('${do_date}',-29),final_amount,0)) final_amount_30d
from dws_order_course_lidan_1d
where dt='${do_date}'
group by course_name;
"
ads_review_info_course_lidan="
insert overwrite table ads_review_info_course_lidan partition(dt='${do_date}')
select course_name,
       total_review_score_1d,
       review_user_num_1d,
       count_review_1d,
       count_good_review_1d,
       total_review_score_7d,
       review_user_num_7d,
       count_review_7d,
       count_good_review_7d,
       total_review_score_30d,
       review_user_num_30d,
       count_review_30d,
       count_good_review_30d
from dws_review_info_course_lidan_1d
where dt='${do_date}'
"
ads_user_retention_course_lidan="
insert overwrite table ads_user_retention_course_lidan partition(dt='${do_date}')
select a.course_name,
       a.try_dt,
       a.diff_day,
       b.try_user_num,
       a.pay_num/b.try_user_num
from
	(
		select course_name,
		       try_dt,
		       diff_day,
		       count(min_order_create_time) pay_num
		from dws_user_retention_course_lidan_1d
		where dt = '${do_date}' and (diff_day>0 or diff_day is null)
		group by course_name, try_dt, diff_day
	)a

left join(
    select course_name,
           try_dt,
           count(user_id) try_user_num
    from dws_user_retention_course_lidan_1d
    where dt='${do_date}' and (diff_day>0 or diff_day is null)
    group by course_name, try_dt
	         ) b
on a.course_name=b.course_name and a.try_dt=b.try_dt
"
ads_traffic_different_source_user_order="
insert overwrite table ads_traffic_different_source_user_order
select * from ads_traffic_different_source_user_order
union
select
        1 date_type,
        os.source_site source_site_1d,
       sum(payment_total_amount) payment_total_amount_1d,
       cast(percent_conversion as DECIMAL(16, 3) )percent_conversion_1d,
       td.dt
from dws_traffic_different_source_user_order_1d td
join ods_base_source_full os on td.sourcetype_code=os.id
where td.dt='${do_date}'
group by os.source_site, td.dt,percent_conversion

union all

select
    7 date_type,
        os.source_site source_site_7d,
       sum(payment_total_amount) payment_total_amount_7d,
       cast(percent_conversion as DECIMAL(16, 3) )percent_conversion_7d,
       td.dt
from dws_traffic_different_source_user_order_1d td
join ods_base_source_full os on td.sourcetype_code=os.id
where td.dt<='${do_date}' and td.dt>=date_sub('${do_date}',7)
group by os.source_site, td.dt,percent_conversion

union all

select
    30 date_type,
        os.source_site source_site_30d,
       sum(payment_total_amount) payment_total_amount_30d,
       cast(percent_conversion as DECIMAL(16, 3) )percent_conversion_30d,
       td.dt
from dws_traffic_different_source_user_order_1d td
join ods_base_source_full os on td.sourcetype_code=os.id
where td.dt<='${do_date}' and td.dt>=date_sub('${do_date}',30)
group by os.source_site, td.dt,percent_conversion;
"
ads_traffic_source_statistics="
insert overwrite table ads_traffic_source_statistics
select * from ads_traffic_source_statistics
union
select 1 as day_type,
        count(distinct mid_id) user_num, --访客数
       sum(during_time_1d)/count(session_id) avg_during_time, --平均会话停留时长
       sum(page_count_1d)/count(session_id) avg_page_num, --平均浏览页面数
       count(session_id) session_num, --会话总数
       sum(if(page_count_1d=1,1,0))/count(session_id) skip_percent--跳出率
from dws_traffic_session_page_view_1d
where dt='${do_date}'

union all
select 7 as day_type,
    count(distinct mid_id) user_num, --访客数
       sum(during_time_1d)/count(session_id) avg_during_time, --平均会话停留时长
       sum(page_count_1d)/count(session_id) avg_page_num, --平均浏览页面数
       count(session_id) session_num, --会话总数
       sum(if(page_count_1d=1,1,0))/count(session_id) skip_percent--跳出率
from dws_traffic_session_page_view_1d
where dt<='${do_date}' and dt>=date_add('${do_date}',-6)

union all
select 30 as day_type,
        count(distinct mid_id) user_num, --访客数
       sum(during_time_1d)/count(session_id) avg_during_time, --平均会话停留时长
       sum(page_count_1d)/count(session_id) avg_page_num, --平均浏览页面数
       count(session_id) session_num, --会话总数
       sum(if(page_count_1d=1,1,0))/count(session_id) skip_percent--跳出率
from dws_traffic_session_page_view_1d
where dt<='${do_date}' and dt>=date_add('${do_date}',-29);
"
ads_traffic_different_page_source_path="
insert overwrite table ads_traffic_different_page_source_path
select * from ads_traffic_different_page_source_path
union
select
    source,
    target,
    sum(if(dt=date_sub('${do_date}',0),path_count_1d,0)) day_1_count,
    sum(if(dt>=date_sub('${do_date}',6),path_count_1d,0)) day_7_count,
    sum(if(dt>=date_sub('${do_date}',29),path_count_1d,0)) day_30_count
from (select dt,
             source,
             target,
             count(1) path_count_1d
      from dws_traffic_different_page_source_path_1d
      where dt <= '${do_date}'
        and dt >= date_sub('${do_date}', 30)
      group by source, target, dt,datediff("${do_date}",dt))t
group by source,    target;
"


case $1 in
    "ads_test_info_by_exam" )
        /opt/module/hive/bin/hive -e "use ${APP};$ads_test_info_by_exam"
    ;;
    "ads_test_info_by_scorestage" )
        /opt/module/hive/bin/hive -e "use ${APP};$ads_test_info_by_scorestage"
    ;;
    "ads_test_info_by_question" )
        /opt/module/hive/bin/hive -e "use ${APP};$ads_test_info_by_question"
    ;;
    "ads_play_course_stats" )
        /opt/module/hive/bin/hive -e "use ${APP};$ads_play_course_stats"
    ;;
    "ads_play_chapter_stats" )
        /opt/module/hive/bin/hive -e "use ${APP};$ads_play_chapter_stats"
    ;;
    "ads_order_by_user" )
        /opt/module/hive/bin/hive -e "use ${APP};$ads_order_by_user"
    ;;
    "ads_order_by_province" )
        /opt/module/hive/bin/hive -e "use ${APP};$ads_order_by_province"
    ;;
    "ads_complete_num" )
        /opt/module/hive/bin/hive -e "use ${APP};$ads_complete_num"
    ;;
    "ads_order_category_lidan" )
        /opt/module/hive/bin/hive -e "use ${APP};$ads_order_category_lidan"
    ;;
    "ads_user_retention_category_lidan" )
        /opt/module/hive/bin/hive -e "use ${APP};$ads_user_retention_category_lidan"
    ;;
    "ads_user_retention_subject_lidan" )
        /opt/module/hive/bin/hive -e "use ${APP};$ads_user_retention_subject_lidan"
    ;;
    "ads_order_subject_lidan" )
        /opt/module/hive/bin/hive -e "use ${APP};$ads_order_subject_lidan"
    ;;
    "ads_order_course_lidan" )
        /opt/module/hive/bin/hive -e "use ${APP};$ads_order_course_lidan"
    ;;
    "ads_review_info_course_lidan" )
        /opt/module/hive/bin/hive -e "use ${APP};$ads_review_info_course_lidan"
    ;;
    "ads_user_retention_course_lidan" )
        /opt/module/hive/bin/hive -e "use ${APP};$ads_user_retention_course_lidan"
    ;;
  "ads_traffic_different_page_source_path" )
        /opt/module/hive/bin/hive -e "use ${APP};$ads_traffic_different_page_source_path"
    ;;
  "ads_traffic_source_statistics" )
        /opt/module/hive/bin/hive -e "use ${APP};$ads_traffic_source_statistics"
    ;;
    "ads_traffic_different_source_user_order" )
        /opt/module/hive/bin/hive -e "use ${APP};$ads_traffic_different_source_user_order"
    ;;

    "all" )
        /opt/module/hive/bin/hive -e "
        use ${APP};
        $ads_test_info_by_exam
        $ads_test_info_by_scorestage
        $ads_test_info_by_question
        $ads_play_course_stats
        $ads_play_chapter_stats
        $ads_order_by_user
        $ads_order_by_province
        $ads_complete_num
        $ads_order_category_lidan
        $ads_user_retention_category_lidan
        $ads_user_retention_subject_lidan
        $ads_order_subject_lidan
        $ads_order_course_lidan
        $ads_review_info_course_lidan
        $ads_user_retention_course_lidan
        $ads_traffic_different_page_source_path
        $ads_traffic_source_statistics
        $ads_traffic_different_source_user_order
        "
    ;;
esac